{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "view-in-github"
   },
   "source": [
    "<a href=\"https://colab.research.google.com/github/tomasonjo/blogs/blob/master/ice%26fire/Ice%26Fire_analysis.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "pK9aJPSTWznM",
    "outputId": "cf054629-bb16-4b56-95b0-2ea596de7eab"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
      "Collecting graphdatascience\n",
      "  Downloading graphdatascience-1.5-py3-none-any.whl (183 kB)\n",
      "\u001b[K     |████████████████████████████████| 183 kB 5.1 MB/s \n",
      "\u001b[?25hCollecting multimethod<2.0,>=1.0\n",
      "  Downloading multimethod-1.9-py3-none-any.whl (10 kB)\n",
      "Requirement already satisfied: pandas<2.0,>=1.0 in /usr/local/lib/python3.7/dist-packages (from graphdatascience) (1.3.5)\n",
      "Requirement already satisfied: tqdm<5.0,>=4.0 in /usr/local/lib/python3.7/dist-packages (from graphdatascience) (4.64.1)\n",
      "Collecting neo4j<6.0,>=4.4.2\n",
      "  Downloading neo4j-5.2.0.tar.gz (173 kB)\n",
      "\u001b[K     |████████████████████████████████| 173 kB 38.6 MB/s \n",
      "\u001b[?25hRequirement already satisfied: pyarrow<11.0,>=4.0 in /usr/local/lib/python3.7/dist-packages (from graphdatascience) (6.0.1)\n",
      "Requirement already satisfied: pytz in /usr/local/lib/python3.7/dist-packages (from neo4j<6.0,>=4.4.2->graphdatascience) (2022.6)\n",
      "Requirement already satisfied: python-dateutil>=2.7.3 in /usr/local/lib/python3.7/dist-packages (from pandas<2.0,>=1.0->graphdatascience) (2.8.2)\n",
      "Requirement already satisfied: numpy>=1.17.3 in /usr/local/lib/python3.7/dist-packages (from pandas<2.0,>=1.0->graphdatascience) (1.21.6)\n",
      "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.7/dist-packages (from python-dateutil>=2.7.3->pandas<2.0,>=1.0->graphdatascience) (1.15.0)\n",
      "Building wheels for collected packages: neo4j\n",
      "  Building wheel for neo4j (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
      "  Created wheel for neo4j: filename=neo4j-5.2.0-py3-none-any.whl size=248021 sha256=7eca51544ff2688bb0223e2c4bad8b9d4f6ad129b0fa70670353498914a04c33\n",
      "  Stored in directory: /root/.cache/pip/wheels/5a/07/16/4d845d69ef310660c14b7148848c95da3ef3950c7b58daec42\n",
      "Successfully built neo4j\n",
      "Installing collected packages: neo4j, multimethod, graphdatascience\n",
      "Successfully installed graphdatascience-1.5 multimethod-1.9 neo4j-5.2.0\n"
     ]
    }
   ],
   "source": [
    "!pip install graphdatascience"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "id": "KdsdF8zSW1yN"
   },
   "outputs": [],
   "source": [
    "from graphdatascience import GraphDataScience\n",
    "\n",
    "host = \"bolt://3.231.25.240:7687\"\n",
    "user = \"neo4j\"\n",
    "password = \"hatchets-visitor-axes\"\n",
    "\n",
    "gds = GraphDataScience(host, auth=(user, password))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "5Un_5pO3XE_S",
    "outputId": "15388f90-713a-409c-a03d-5c819caa2fc0"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Tyrion Lannister',\n",
       " 'Sansa Stark',\n",
       " 'Eddard Stark',\n",
       " 'Lyarra Stark',\n",
       " 'Rodrik Stark',\n",
       " 'Beron Stark',\n",
       " 'Brandon Stark',\n",
       " 'Cregan Stark',\n",
       " 'Rickon Stark',\n",
       " 'Sara Snow',\n",
       " 'Jacaerys Velaryon',\n",
       " 'Rhaenyra Targaryen',\n",
       " 'Viserys I']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Results of the query changed for notebook environment\n",
    "gds.run_cypher(\n",
    "    \"\"\"MATCH (s:Character {name:$person1}), (t:Character {name:$person2})\n",
    "MATCH p=shortestPath((s)-[:FATHER|MOTHER|SPOUSE*]-(t))\n",
    "RETURN [n in nodes(p) | n.name] AS result\"\"\",\n",
    "    {\"person1\": \"Tyrion Lannister\", \"person2\": \"Viserys I\"},\n",
    ")[\"result\"][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "2YqgteubXto8",
    "outputId": "c55f2dfa-95e9-4b03-ea9c-8ca17c6f8d78"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         [Alerie Hightower MOTHER to Margaery Tyrell]\n",
       "1    [Alerie Hightower MOTHER to Margaery Tyrell, L...\n",
       "2              [Mace Tyrell FATHER to Margaery Tyrell]\n",
       "3    [Mace Tyrell FATHER to Margaery Tyrell, Olenna...\n",
       "4    [Mace Tyrell FATHER to Margaery Tyrell, Olenna...\n",
       "5    [Mace Tyrell FATHER to Margaery Tyrell, Luthor...\n",
       "Name: result, dtype: object"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Results of the query changed for notebook environment\n",
    "gds.run_cypher(\n",
    "    \"\"\"MATCH p=(c:Character {name:$person})-[:FATHER|MOTHER*]->()\n",
    "RETURN [r in relationships(p) | endNode(r).name + \" \" + type(r) + \" to \" + startNode(r).name] AS result\"\"\",\n",
    "    {\"person\": \"Margaery Tyrell\"},\n",
    ")[\"result\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "id": "t9JdS7ibW4Q0"
   },
   "outputs": [],
   "source": [
    "G, res = gds.graph.project(\"family\", \"Character\", [\"MOTHER\", \"FATHER\", \"SPOUSE\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "id": "MzJFNjK-W_tm"
   },
   "outputs": [],
   "source": [
    "wcc_df = gds.wcc.stream(G)\n",
    "wcc_df[\"name\"] = [el[\"name\"] for el in gds.util.asNodes(wcc_df[\"nodeId\"].to_list())]\n",
    "wcc_df[\"last_name\"] = [\n",
    "    el.split(\" \")[-1] if len(el.split(\" \")) > 1 and len(el.split(\" \")[-1]) > 3 else None\n",
    "    for el in wcc_df[\"name\"]\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "id": "7IciN9YNZMmQ",
    "outputId": "6b0bb5a4-c962-417c-c7d1-4fc050f92873"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nodeId</th>\n",
       "      <th>componentId</th>\n",
       "      <th>name</th>\n",
       "      <th>last_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>119</td>\n",
       "      <td>0</td>\n",
       "      <td>A certain man</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>120</td>\n",
       "      <td>1</td>\n",
       "      <td>Abelar Hightower</td>\n",
       "      <td>Hightower</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>121</td>\n",
       "      <td>2</td>\n",
       "      <td>Abelon</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>122</td>\n",
       "      <td>3</td>\n",
       "      <td>Addam</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>123</td>\n",
       "      <td>4</td>\n",
       "      <td>Addam Frey</td>\n",
       "      <td>Frey</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   nodeId  componentId              name  last_name\n",
       "0     119            0     A certain man       None\n",
       "1     120            1  Abelar Hightower  Hightower\n",
       "2     121            2            Abelon       None\n",
       "3     122            3             Addam       None\n",
       "4     123            4        Addam Frey       Frey"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wcc_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "id": "R9fThQtGZPOT",
    "outputId": "c426bf7c-a396-4009-99b7-283e6b433a4d"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>componentId</th>\n",
       "      <th>componentSize</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>457</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>111</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>938</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>193</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   componentId  componentSize\n",
       "0            5            785\n",
       "1          457             19\n",
       "2          111             12\n",
       "3          938             11\n",
       "4          193             10"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wcc_df.groupby(\"componentId\").size().sort_values(ascending=False).to_frame(\n",
    "    \"componentSize\"\n",
    ").reset_index().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 363
    },
    "id": "HMJgmlRHZpOQ",
    "outputId": "403ca797-78e5-4af8-8f0a-cd7f04d65f36"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>last_name</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Frey</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Targaryen</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Stark</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Lannister</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Hightower</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Velaryon</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Baratheon</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Greyjoy</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Rivers</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Arryn</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   last_name  count\n",
       "0       Frey     91\n",
       "1  Targaryen     66\n",
       "2      Stark     50\n",
       "3  Lannister     30\n",
       "4  Hightower     28\n",
       "5   Velaryon     21\n",
       "6  Baratheon     21\n",
       "7    Greyjoy     19\n",
       "8     Rivers     15\n",
       "9      Arryn     15"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "largest_component = wcc_df.groupby('componentId').size().sort_values(\n",
    "    ascending=False\n",
    ").reset_index()['componentId'][0]\n",
    "wcc_df[wcc_df[\"componentId\"] == largest_component].groupby(\"last_name\").size().sort_values(\n",
    "    ascending=False\n",
    ").to_frame(\"count\").reset_index().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 81
    },
    "id": "Mll03EJPj6ew",
    "outputId": "cb78ae3a-ea0c-42da-941a-3a9bc4ef90c5"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>spouse1</th>\n",
       "      <th>spouse2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jacaerys Velaryon</td>\n",
       "      <td>Sara Snow</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             spouse1    spouse2\n",
       "0  Jacaerys Velaryon  Sara Snow"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gds.run_cypher(\"\"\"\n",
    "MATCH (c1:Character)<-[:FATHER|MOTHER]-(s1)-[:SPOUSE]-(s2)-[:FATHER|MOTHER]->(c2:Character)\n",
    "WHERE c1.name CONTAINS \"Targaryen\" AND c2.name CONTAINS \"Stark\"\n",
    "RETURN s1.name AS spouse1, s2.name AS spouse2\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "PT-pSDn8an6s",
    "outputId": "bc5a9e48-bb95-47ea-a3ab-cebf40ddba5f"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "graphName                                                           family\n",
       "database                                                             neo4j\n",
       "memoryUsage                                                               \n",
       "sizeInBytes                                                             -1\n",
       "nodeCount                                                             3653\n",
       "relationshipCount                                                     1794\n",
       "configuration            {'relationshipProjection': {'FATHER': {'orient...\n",
       "density                                                           0.000134\n",
       "creationTime                           2023-02-01T12:28:33.121376800+00:00\n",
       "modificationTime                       2023-02-01T12:28:33.233263361+00:00\n",
       "schema                   {'graphProperties': {}, 'relationships': {'FAT...\n",
       "schemaWithOrientation    {'graphProperties': {}, 'relationships': {'FAT...\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "G.drop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "XmmwDND9a7qL"
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "include_colab_link": true,
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
